Grokking Database Fundamentals for Tech Interviews
Rate this course
Ask Author
Contribute
Back to course home

0% completed

Vote For New Content
Introduction to Database Systems
Course Overview
Overview of Databases
Core Components of Database
Database Architectures
Relational Vs. Non-relational Databases
Data Storage in DBMS
Data Storage Fundamentals
File Organization in DBMS
Data Compression and Encoding
Quiz
Indexing in DBMS
Introduction to Database Indexes
Single Level Indexing
Tree-Based Indexing Techniques
Hash-Based Indexing
Bloom Filters
Quiz
Transaction Management
Understanding the Transaction
Atomicity
Consistency
Isolation
Durability
Quiz
Concurrency Control
Understanding the Concurrency Control
Optimistic vs. Pessimistic Concurrency Control
Lock-Based Concurrency Control
Timestamp-Based Concurrency Control
Multiversion Concurrency Control (MVCC)
Quiz
Fundamentals of Distributed Databases
Partitioning in Databases
Partitioning Types
Sharding in Databases
Distributed Query Processing
Quiz
Replication in Databases
Replication Topologies
Understanding the Consistency Models in Databases
The CAP Theorem and Its Implications
Quiz
Understanding the Consensus
Consensus algorithms
Leader Election Strategies
Quiz
Understanding the Fault Tolerance
Logging Mechanisms in Databases
Checkpointing and Recovery Basics
Coordinated Recovery in Distributed Systems
High Availability Through Replication and Failover
Ensuring Data Resilience: Redundancy, Mirroring, and Recovery
Quiz
Introduction to NoSQL
Key-Value Stores
Document-Oriented Databases
Column-Family and Wide-Column Stores
Graph Databases
Quiz
Quora System Design & Database Design
TikTok System Design & Database Design
Airbnb System Design & Database Design
Snapchat System Design & Database Design
Multiversion Concurrency Control (MVCC)

What is Multiversion Concurrency Control (MVCC)?

MVCC is a concurrency control mechanism used in databases to allow multiple transactions to operate on the same data simultaneously by maintaining multiple versions of data items. Instead of locking rows, MVCC creates a new version of a row whenever a write occurs, ensuring that:

  • Transactions can read consistent snapshots of the database.
  • Write operations do not block read operations.

How MVCC Works

  1. Data Versioning:

    • Each data item in the database is associated with multiple versions, each identified by a transaction.
    • Each version contains metadata, such as:
      • created_by: The transaction that created the version.
      • deleted_by: The transaction that invalidated or deleted the version.
  2. Snapshot Isolation:

    • Transactions work on a snapshot of the database taken at their start time.
    • Changes made by other transactions are not visible until committed.
  3. Conflict Resolution:

    • MVCC avoids conflicts by allowing reads and writes to occur on different versions of the same data.

Example

In the image below, data versions are created as transactions modify balances in two accounts.

Image

Initial State (T1):

The database starts with two accounts:

  • Account A has an initial balance of $500.
  • Account B has an initial balance of $300.

Each account has a version associated with it, created by the transaction that initialized the data. Both accounts currently have no deleted versions.

Transaction 1 (T2): Increase Account A’s Balance by $200

  • A new version of Account A is created to reflect the updated balance of $700.
  • The previous version of Account A, which had a balance of $500, is marked as invalid (deleted) by the current transaction.
  • Account B remains unchanged, as this transaction does not interact with it.

Transaction 2 (T3): Decrease Account A’s Balance by $100

  • Another new version of Account A is created, reducing the balance to $600.
  • The version created in Transaction 1, with a balance of $700, is now marked as invalid (deleted) by this transaction.
  • Similar to the previous transaction, Account B remains unaffected.

Key Points in the Example

  1. Version Creation:

    • Every write operation creates a new version of the data.
    • The deleted_by field indicates which transaction invalidated the previous version.
  2. Consistent Snapshots:

    • Transactions can read the most recent committed version of a row that existed at their start time.
    • For example, while Transaction 3 updates Account A, another transaction can still read the previous version with a balance of $500.
  3. No Locks:

    • MVCC eliminates the need for locks, allowing reads and writes to occur simultaneously.

Real-World Use Cases

  1. E-Commerce Platforms: Users can browse product listings while others update inventory or prices.
  2. Banking Systems: Customers can view account balances while transactions are being processed.
  3. Content Management Systems: Editors can read and edit content simultaneously without conflicts.
Timestamp-Based Concurrency Control
Quiz
Mark as Completed

On this page

What is Multiversion Concurrency Control (MVCC)?

How MVCC Works

Example

Initial State (T1):

Transaction 1 (T2): Increase Account A’s Balance by $200

Transaction 2 (T3): Decrease Account A’s Balance by $100

Key Points in the Example

Real-World Use Cases